﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using log4net;
using log4net.Config;
namespace PlayerStat
{
    public class DBUtil
    {
        private ILog logger;
        public DBClassDataContext dbClass;
        
        public DBUtil()
        {
            logger = LogManager.GetLogger("scrape");
            dbClass = new DBClassDataContext();
        }

        //Find the max date in DB of a league
        public bool FindMaxDate(int leagueID, ref DateTime dt)
        {
            try
            {
                var result = (from d in dbClass.tblMatches
                             where d.LeagueID == leagueID
                             select d).Max(match => match.EventTime);
                if (result.HasValue)
                {
                    dt = result.Value.Date;
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch(Exception ex)
            {
                logger.Error("#FindMaxDate# Failed: Exception.");
                logger.Error(ex.ToString());
                return false;
            }
        }

        //Find the min date in DB of a league
        public bool FindMinDate(int leagueID, ref DateTime dt)
        {
            try
            {
                var result = (from d in dbClass.tblMatches
                              where d.LeagueID == leagueID
                              select d).Min(match => match.EventTime);
                if (result.HasValue)
                {
                    dt = result.Value.Date;
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                logger.Error("#FindMinDate# Failed: Exception.");
                logger.Error(ex.ToString());
                return false;
            }
        }

        // find the max round in db of a league
        public bool FindMaxRound(int leagueID, ref DateTime dt, ref int rd)
        {
            try
            {
                var result = (from d in dbClass.tblMatches
                              where d.LeagueID == leagueID
                              select d).Max(match => match.EventTime);
                if (result.HasValue)
                {
                    //Find the max event date in DB
                    dt = result.Value.Date;
                    var rt = from r in dbClass.tblMatches 
                             where  r.LeagueID == leagueID && r.EventTime == result.Value
                             select r;
                    //Find the round related to the max event date
                    if (rt.First().MatchRound.HasValue)
                    {
                        rd = rt.First().MatchRound.Value;
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                logger.Error("#FindMaxDate# Failed: Exception.");
                logger.Error(ex.ToString());
                return false;
            }
        }

        // remove the data by match date
        public bool ClearDataByDay(int leagueID, DateTime day)
        {
            return true;
            //try
            //{
            //    var matches = from m in dbClass.tblMatches
            //                  where m.EventTime.Value.Date == day.Date
            //                  select m;
            //    dbClass.tblMatches.DeleteAllOnSubmit(matches);
            //    dbClass.SubmitChanges();
            //    return true;
            //}
            //catch (Exception ex)
            //{
            //    logger.Error("#ClearLastDateData# Failed: Exception.");
            //    logger.Error(ex.ToString());
            //    return false;
            //}
        }

        //Get team id 
        public bool GetTeamID(int leagueID, string teamName, ref int teamID)
        {
            teamID = 0;
            try
            {
                var result = from t in dbClass.tblTeams
                             where t.TeamName == teamName
                             select t ;
                if (result.Count() == 0)
                {
                    teamID = -1;
                    return false;
                }
                else if (result.Count() == 1)
                {
                    
                    teamID = result.First().TeamID;
                }
                else
                {
                    logger.Debug("#GetTeamID# Ambiguous team found.");
                    return false;
                }
                return true;
            }
            catch(Exception ex)
            {
                logger.Error("#GetTeamID# Failed: Exception.");
                logger.Error(ex.ToString());
                return false;
            }
        }

        //Get player list
        public bool GetPlayer(string playerName, out List<tblPlayer> playerList)
        {
            try
            {
                var result = from t in dbClass.tblPlayers
                             where t.PlayerName == playerName
                             orderby t.PlayerKey
                             select t;
                if (result.Count() == 0)
                {
                    playerList = null;
                    return false;
                }
                else
                {
                    playerList = result.ToList();
                    return true;
                }
            }
            catch (Exception ex)
            {
                logger.Error("#GetPlayerID# Failed: Exception.");
                logger.Error(ex.ToString());
                playerList = null;
                return false;
            }
        }
        
        public bool ExistInDB(string home, string away, DateTime eventTime)
        {
            var result1 = from t in dbClass.tblTeams
                          where t.TeamName == home
                          select t;
            if (result1.Count() == 0)
            {
                return false;
            }
            var result2 = from t in dbClass.tblTeams
                          where t.TeamName == away
                          select t;
            if (result2.Count() == 0)
            {
                return false;
            }
            var result3 = from t in dbClass.tblMatches
                          where t.EventTime == eventTime 
                            && t.HomeTeamID == result1.First().TeamID 
                            && t.AwayTeamID == result2.First().TeamID
                          select t;
            if (result3.Count() == 0)
            {
                return false;
            }
            return true;
        }
    }
}
